/*
 *  Copyright (c) 1998, Regents of the University of California
 *  All rights reserved.
 *  Redistribution and use in source and binary forms, with or without
 *  modification, are permitted provided that the following conditions are met:
 * 
 *      * Redistributions of source code must retain the above copyright
 *        notice, this list of conditions and the following disclaimer.
 *      * Redistributions in binary form must reproduce the above copyright
 *        notice, this list of conditions and the following disclaimer in the
 *        documentation and/or other materials provided with the distribution.
 *      * Neither the name of the University of California, Berkeley nor the
 *        names of its contributors may be used to endorse or promote products
 *        derived from this software without specific prior written permission.
 * 
 *  THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS ``AS IS'' AND ANY
 *  EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 *  WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 *  DISCLAIMED. IN NO EVENT SHALL THE REGENTS AND CONTRIBUTORS BE LIABLE FOR ANY
 *  DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 *  (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 *  LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 *  ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 *  (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 *  SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */
package org.demis.elf.dbreader.postgresql;

import java.io.IOException;
import java.io.StringWriter;
import java.sql.Array;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.velocity.Template;
import org.apache.velocity.VelocityContext;
import org.apache.velocity.exception.MethodInvocationException;
import org.apache.velocity.exception.ParseErrorException;
import org.apache.velocity.exception.ResourceNotFoundException;
import org.demis.elf.Column;
import org.demis.elf.ColumnSortSequence;
import org.demis.elf.Index;
import org.demis.elf.PrimaryKey;
import org.demis.elf.ForeignKey;
import org.demis.elf.Schema;
import org.demis.elf.Table;
import org.demis.elf.UniqueConstraint;
import org.demis.elf.configuration.DatabaseConfiguration;
import org.demis.elf.dbreader.AbstractSchemaReader;
import org.demis.elf.dbreader.generic.GenericSchemaReader;

/**
 * @version 1.0
 * @author <a href="mailto:demis27@demis27.net">Stéphane kermabon</a>
 */
public class PostgresqlSchemaReader extends AbstractSchemaReader {

    private final Log logger = LogFactory.getLog(PostgresqlSchemaReader.class);

    public PostgresqlSchemaReader() {
        // no op
    }

    // TODO : read Column IS_AUTOINCREMENT
    @Override
    public Schema read(DatabaseConfiguration configuration) {
        return super.read(configuration);
    }

    @Override
    public void readColumnsDefaultValue(DatabaseConfiguration configuration, Schema schema) {
        Connection connection = null;
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        
        try {
            if (configuration.getUser() != null) {
                connection = DriverManager.getConnection(configuration.getUrl(), configuration.getUser(), configuration.getPassword());
            } else {
                connection = DriverManager.getConnection(configuration.getUrl());
            }
            DatabaseMetaData metaData = connection.getMetaData();
            for (Table table : schema.getTables()) {
                for (Column column: table.getColumns()) {
                    Template template = initVelocity("scripts/postgresql/defaultValue.sql.vm");
                    VelocityContext context = new VelocityContext();
                    context.put("column", column);
                    StringWriter writer = new StringWriter();
                    template.merge(context, writer);
                    logger.info("default value request : " + writer.toString());
                    statement = connection.prepareStatement(writer.toString());
                    resultSet = statement.executeQuery();
                    if (resultSet.next()) {
                        column.setDefaultValue(resultSet.getString("defaultValue"));
                        logger.info("find default value for " + column.getTable().getSchema().getName() + "." + column.getTable().getName() + "." + column.getName() + " : " + column.getDefaultValue());
                    }
                }
            }
        } catch (ResourceNotFoundException ex) {
            Logger.getLogger(PostgresqlSchemaReader.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseErrorException ex) {
            Logger.getLogger(PostgresqlSchemaReader.class.getName()).log(Level.SEVERE, null, ex);
        } catch (MethodInvocationException ex) {
            Logger.getLogger(PostgresqlSchemaReader.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(PostgresqlSchemaReader.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(PostgresqlSchemaReader.class.getName()).log(Level.SEVERE, configuration.getUrl(), ex);
        } finally {
            cleanConnection(connection, statement, resultSet);
        }
    }

    @Override
    public void readUniqueConstraint(DatabaseConfiguration configuration, Schema schema) {
        Connection connection = null;
        ResultSet resultSet = null;
        Statement statement = null;

        try {
            if (configuration.getUser() != null) {
                connection = DriverManager.getConnection(configuration.getUrl(), configuration.getUser(), configuration.getPassword());
            } else {
                connection = DriverManager.getConnection(configuration.getUrl());
            }

            String constraintList = "select pg_constraint.conname as constraintName, "
                    + " pg_constraint.conkey as columns, pg_class.relname as tableName"
                    + " from pg_constraint, pg_class where contype = 'u' "
                    + " and pg_constraint.conrelid = pg_class.relfilenode ";

            statement = connection.createStatement();
            resultSet = statement.executeQuery(constraintList);

            while (resultSet.next()) {
                UniqueConstraint uniqueConstraint = new UniqueConstraint();
                uniqueConstraint.setName(resultSet.getString("constraintName"));
                Array columns = resultSet.getArray("columns");
                String tableName = resultSet.getString("tableName");

                Table table = schema.getTable(tableName);
                uniqueConstraint.setTable(table);

                ResultSet arrayResultSet = columns.getResultSet();
                int constraintColumnPosition = 1;
                while (arrayResultSet.next()) {
                    int columnPosition = resultSet.getInt(1);
                    Column column = table.getColumn(columnPosition);
                    uniqueConstraint.addColumn(column, constraintColumnPosition++);
                }
                table.addUniqueConstraint(uniqueConstraint);
            }

        } catch (SQLException ex) {
            Logger.getLogger(PostgresqlSchemaReader.class.getName()).log(Level.SEVERE, configuration.getUrl(), ex);
        } finally {
            cleanConnection(connection, statement, resultSet);
        }
    }

    @Override
    protected String getSchemaNameForMetaData(String schemaName) {
        return schemaName;
    }

    @Override
    protected String getTableNameForMetaData(String tableName) {
        return tableName;
    }
}
